package com.ezlcp.form.db;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson2.JSONObject;
import com.ezlcp.commons.constant.Constants;
import com.ezlcp.commons.constant.DataTypeEnum;
import com.ezlcp.commons.tool.DateUtils;
import com.ezlcp.commons.tool.IdGenerator;
import com.ezlcp.commons.tool.StringUtils;
import com.ezlcp.commons.utils.ContextUtil;
import com.ezlcp.commons.utils.SpringUtil;
import com.ezlcp.form.entity.Entity;
import com.ezlcp.form.entity.EntityCol;
import com.ezlcp.form.entity.EntityIndex;
import com.ezlcp.form.service.SerialNoServiceImpl;
import com.github.drinkjava2.jdialects.*;
import com.github.drinkjava2.jdialects.model.ColumnModel;
import com.github.drinkjava2.jdialects.model.TableModel;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author Elwin ZHANG
 * @description: 默认的数据库操作类<br />
 * @date 2023/7/6 17:11
 */
@Slf4j
public class DBClient implements IDbClient {
    private static final String TABLE_NAME = "TABLE_NAME";
    //当前的数据源
    private DruidDataSource dataSource;
    //当前的JdbcTemplate
    private JdbcTemplate jdbcTemplate;
    //当前的数据库方言
    private Dialect dialect;

    public DBClient(DruidDataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.dialect = Dialect.guessDialect(dataSource);
    }

    /***
     * @description 获取当前对象的数据源
     * @return com.alibaba.druid.pool.DruidDataSource
     * @author Elwin ZHANG
     * @date 2023/7/13 16:12
     */
    @Override
    public DruidDataSource getDataSource() {
        return this.dataSource;
    }

    /***
     * @description 获取当前对象的JdbcTemplate
     * @return org.springframework.jdbc.core.JdbcTemplate
     * @author Elwin ZHANG
     * @date 2023/7/13 16:11
     */
    @Override
    public JdbcTemplate getJdbcTemplate() {
        return this.jdbcTemplate;
    }

    /***
     * @description 获取当前的数据库方言
     * @return com.github.drinkjava2.jdialects.Dialect
     * @author Elwin ZHANG
     * @date 2023/7/14 14:54
     */
    public Dialect getDialect() {
        return this.dialect;
    }

    /***
     * @description 判断实体对应的表（视图）是否存在
     * @param entity 实体对象
     * @return int 0不存在，1存在无数据，2 存在且有数据
     * @author Elwin ZHANG
     * @date 2023/7/6 16:49
     */
    @Override
    public int existTable(Entity entity) {
        String tableName = entity.getTableName();
        // String sql = "select count(*) from " + tableName;
        // 用exists效率会更高，但可能兼容性较差
        String sql = "select exists (select * from " + tableName + "  ) as Field1";
        try {
            var result = jdbcTemplate.queryForObject(sql, Boolean.class);
            if (result) {
                return TableStatusEnum.HasData.getValue();
            }
            return TableStatusEnum.Empty.getValue();
        } catch (Exception e) {
            //表不存在
            return TableStatusEnum.NotExist.getValue();
        }
    }

    /***
     * @description 创建表
     * @param entity 实体对象
     * @return java.lang.String
     * @author Elwin ZHANG
     * @date 2023/7/6 16:51
     */
    @Override
    public boolean createTable(Entity entity) {
        TableModel tableModel = new TableModel(entity.getTableName());
        tableModel.comment(entity.getEntName());
        tableModel.column(Constants.COL_ID).VARCHAR(64).pkey().comment("主键");
        //循环处理字段
        for (var col : entity.getCols()) {
            var cm = getColModel(col, this.dialect);
            if (cm != null) {
                tableModel.addColumn(cm);
            }
        }
        String[] sqlArr = this.dialect.toCreateDDL(tableModel);
        for (String ddl : sqlArr) {
            jdbcTemplate.execute(ddl);
        }
        return true;
    }


    /***
     * @description 添加列定义(主键除外)
     * @param col 列定义数据
     * @param dialect 数据库方言
     * @author Elwin ZHANG
     * @date 2023/7/10 13:22
     */
    private ColumnModel getColModel(EntityCol col, Dialect dialect) {
        if (Constants.COL_ID.equals(col.getFieldName())) {
            return null;
        }
        ColumnModel columnModel = new ColumnModel(col.getFieldName());
        columnModel.comment(col.getShowName());
        String colType = col.getColType();
        int length = col.getColLength() == null ? 0 : col.getColLength();
        if (DataTypeEnum.SmallInt.getValue().equals(colType)) {
            columnModel.SMALLINT();
        } else if (DataTypeEnum.Int.getValue().equals(colType)) {
            columnModel.INTEGER();
        } else if (DataTypeEnum.BigInt.getValue().equals(colType)) {
            columnModel.BIGINT();
        } else if (DataTypeEnum.Decimal.getValue().equals(colType)) {
            int prec = col.getColPrec() == null ? 0 : col.getColPrec();
            columnModel.DECIMAL(length, prec);
        } else if (DataTypeEnum.Text.getValue().equals(colType)) {
            columnModel.CLOB();
        } else if (DataTypeEnum.LongText.getValue().equals(colType)) {
            //只有MySQL/MariaDB 才有LongText类型，它的text类型最大只有64K
            if (dialect.isMySqlFamily() || dialect.isFamily("MariaDB")) {
                columnModel.setColumnType(Type.LONGTEXT);
            } else {
                columnModel.CLOB();
            }
        } else if (DataTypeEnum.Date.getValue().equals(colType)) {
            columnModel.DATE();
        } else if (DataTypeEnum.Datetime.getValue().equals(colType)) {
            columnModel.TIMESTAMP();
        } else if (DataTypeEnum.VarChar.getValue().equals(colType)) {
            if (dialect.isOracleFamily()) {
                columnModel.setColumnType(Type.VARCHAR2);
                columnModel.setLength(length);
            } else {
                columnModel.VARCHAR(length);
            }

        }
        //列不为空
        if (col.getIsRequired() == 1) {
            columnModel.notNull();
        }
        //列默认值
        String defaultValue = col.getDefaultVal();
        if (StringUtils.isNotEmpty(defaultValue)) {
            columnModel.defaultValue(defaultValue);
        }
        return columnModel;
    }

    /***
     * @description 删除实休对应的物理表
     * @param entity 实体对象
     * @return boolean
     * @author Elwin ZHANG
     * @date 2023/7/6 17:07
     */
    @Override
    public boolean dropTable(Entity entity) {
        String sql = dropTableSql(entity);
        getJdbcTemplate().execute(sql);
        return true;
    }

    /***
     * @description 获取删除表的SQL
     * @param entity 实体
     * @return java.lang.String
     * @author Elwin ZHANG
     * @date 2023/7/14 10:31
     */
    @Override
    public String dropTableSql(Entity entity) {
        Dialect dialect = Dialect.guessDialect(this.getDataSource());
        return dialect.dropTableDDL(entity.getTableName());
    }

    /***
     * @description 获取表结构变化的SQL语句
     * @param entity 实体对象
     * @return java.util.List<java.lang.String>
     * @author Elwin ZHANG
     * @date 2023/7/14 10:49
     */
    @Override
    public String[] alterTableSql(Entity entity) {
        var tableModel = getTableModel(entity.getTableName());
        //表不存在
        if (tableModel == null || tableModel.getColumns().isEmpty()) {
            return null;
        }
        List<String> sqls = new ArrayList<>();
        //循环检查第个实体列
        for (var col : entity.getCols()) {
            generateColSql(col, tableModel, sqls);
        }
        return sqls.toArray(new String[sqls.size()]);
    }

    /***
     * @description 对于已经存在数据的表，某列的定义修改时，要先生成SQL，再交由客户决定是否执行
     * @param col 实体列定义
     * @param tableModel 当前物理表模型
     * @param sqls 生成的SQL列表
     */
    private void generateColSql(EntityCol col, TableModel tableModel, List<String> sqls) {
        //主键列，跳过
        if (Constants.COL_ID.equals(col.getFieldName())) {
            return;
        }
        var curCol = getColModel(col, dialect);
        curCol.setTableModel(new TableModel(tableModel.getTableName()));
        for (var cm : tableModel.getColumns()) {
            //存在同名列
            if (curCol.getColumnName().equals(cm.getColumnName())) {
                boolean isSame = false;
                //类型、默认值、可为空也相同
                if (isSameType(curCol, cm) && curCol.getNullable() == cm.getNullable()
                        && StringUtils.compare(curCol.getDefaultValue(), cm.getDefaultValue()) == 0) {
                    return;
                }
                //同名但定义不同列，则删除后重建
                var drops = dialect.toDropColumnDDL(cm);
                sqls.addAll(List.of(drops));
                var news = dialect.toAddColumnDDL(curCol);
                sqls.addAll(List.of(news));
                return;
            }
        }
        //没在找到同名列，则新增当前列
        var adds = dialect.toAddColumnDDL(curCol);
        sqls.addAll(List.of(adds));
    }

    /***
     * @description 判断两个列的数据类型是否相同
     * @param model1 列1
     * @param model2 列2
     * @return boolean
     */
    private boolean isSameType(ColumnModel model1, ColumnModel model2) {
        if (model1.getColumnType().equals(Type.CLOB)) {
            if (model2.getColumnType().equals(Type.CLOB)) {
                return true;
            }
            if ((model2.getColumnType().equals(Type.VARCHAR) || model2.getColumnType().equals(Type.LONGVARCHAR) ||
                    model2.getColumnType().equals(Type.VARCHAR2)) && model2.getLength() > 65536) {
                return true;
            }
            return false;
        } else if (model1.getColumnType().equals(Type.VARCHAR) || model1.getColumnType().equals(Type.VARCHAR2)) {
            if ((model2.getColumnType().equals(Type.VARCHAR) || model2.getColumnType().equals(Type.VARCHAR2))
                    && model1.getLength() == model2.getLength()) {
                return true;
            }
            return false;
        } else if (model1.getColumnType().equals(Type.DECIMAL)) {
            if (model2.getColumnType().equals(Type.DECIMAL) && model2.getPrecision() == model1.getPrecision() && model2.getScale() == model1.getScale()) {
                return true;
            }
            return false;
        } else {
            return model1.getColumnType().equals(model2.getColumnType());
        }
    }

    /***
     * @description 根据表名获取数据库中定义信息
     * @param tableName 物理表名
     * @return com.github.drinkjava2.jdialects.model.TableModel
     * @author Elwin ZHANG
     * @date 2023/7/14 14:46
     */
    @Override
    public TableModel getTableModel(String tableName) {
        Connection con = null;
        try {
            con = getDataSource().getConnection();
            return db2Models(con, this.dialect, tableName);
        } catch (Exception e) {
            return null;
        } finally {
            //释放数据库连接
            if (con != null) {
                try {
                    con.close();
                } catch (Exception e) {
                    log.error("关闭数据库连接异常", e);
                }
            }
        }
    }

    /***
     * @description 检查SQL语句数组中是否含用drop命令
     * @param sqlArray sql数组
     * @return boolean
     * @author Elwin ZHANG
     * @date 2023/7/17 11:51
     */
    @Override
    public boolean containDropSql(String[] sqlArray) {
        if (sqlArray == null || sqlArray.length == 0) {
            return false;
        }
        for (var sql : sqlArray) {
            if (sql.toLowerCase().indexOf("drop ") > 0) {
                return true;
            }
        }
        return false;
    }

    /***
     * @description 执行多个SQL语名
     * @param sqlArray Sql数组
     */
    @Override
    public void executeSql(String[] sqlArray) {
        if (sqlArray == null || sqlArray.length == 0) {
            return;
        }
        for (var sql : sqlArray) {
            this.getJdbcTemplate().execute(sql);
        }
    }

    /***
     * @description 删除表索引
     * @param indexName 索引名
     * @param tableName 表名
     */
    @Override
    public void dropIndex(String indexName, String tableName) {
        String sql1 = "DROP INDEX " + indexName;
        String sql2 = sql1 + " ON " + tableName;
        if (this.dialect.isSQLServerFamily() || this.dialect.isMySqlFamily()) {
            try {
                this.getJdbcTemplate().execute(sql2);
            } catch (Exception e2) {
                log.info(this.dialect.name + "不支持此删除索引语句:" + sql2, e2);
            }
            return;
        }
        if (this.dialect.isOracleFamily() || this.dialect.isPostgresFamily()) {
            try {
                this.getJdbcTemplate().execute(sql1);
            } catch (Exception e1) {
                log.info(this.dialect.name + "不支持此删除索引语句:" + sql1, e1);
            }
            return;
        }
        //其他未知数据库,两种语法语名都执行
        try {
            this.getJdbcTemplate().execute(sql1);
        } catch (Exception e1) {
            log.info(this.dialect.name + "不支持此删除索引语句:" + sql1, e1);
        }
        try {
            this.getJdbcTemplate().execute(sql2);
        } catch (Exception e2) {
            log.info(this.dialect.name + "不支持此删除索引语句:" + sql2, e2);
        }
    }

    /***
     * @description 创建索引
     * @param index 索引对象
     * @param tableName 表名
     */
    @Override
    public void createIndex(EntityIndex index, String tableName) {
        String sql = "CREATE ";
        if (index.getIsUnique() == 1) {
            sql += "UNIQUE ";
        }
        sql += " INDEX " + index.getIndexName() + " ON " + tableName + "(";
        sql += index.getFields() + ")";
        try {
            this.getJdbcTemplate().execute(sql);
        } catch (Exception e1) {
            log.info(this.dialect.name + "不支持此创建索引语句:" + sql, e1);
        }
    }

    /***
     * 根据主键查询表单记录
     * @param tableName 物理表名
     * @param pkId 记录ID
     * @return java.util.Map<java.lang.String, java.lang.Object>
     */
    @Override
    public Map<String, Object> getById(String tableName, String pkId) {
        String sql = "select * from " + tableName + " where id='" + pkId + "'";
        try {
            var list = this.getJdbcTemplate().queryForList(sql);
            if (list != null && !list.isEmpty()) {
                return list.get(0);
            }
        } catch (Exception e1) {
            log.info(this.dialect.name + "查询记录出错:" + sql, e1);
        }
        return null;
    }

    /***
     * 根据主键逻辑删除表单记录
     * @param tableName 物理表名
     * @param pkId 记录ID
     * @return boolean
     */
    @Override
    public boolean tombstoneById(String tableName, String pkId) {
        String sql = "update " + tableName + " set status = 4 where id='" + pkId + "'";
        return executeNativeSql(sql, "逻辑删除记录出错");
    }

    /***
     * 根据主键物理删除表单记录
     * @param tableName 物理表名
     * @param pkId 记录ID
     * @return boolean
     */
    @Override
    public boolean deleteById(String tableName, String pkId) {
        String sql = "delete from " + tableName + " where id='" + pkId + "'";
        return executeNativeSql(sql, "物理删除记录出错");
    }

    /***
     * 先转为本地SQL再执行；
     * @param sql 原始SQL
     * @param logMsgPrefix 日志提示前导文字：
     * @return boolean
     */
    private boolean executeNativeSql(String sql, String logMsgPrefix) {
        try {
            var sqlNew = convert2Native(sql);
            this.jdbcTemplate.execute(sqlNew);
            return true;
        } catch (Exception e1) {
            log.info(this.dialect.name + logMsgPrefix + ": " + sql, e1);
            return false;
        }
    }

    /***
     * 新增实体表相关的记录
     * @param entity 实体对象，包括字段列定义
     * @param record 记录数据
     */
    @Override
    public boolean insert(Entity entity, JSONObject record) {
        String insert = "insert into " + entity.getTableName() + " (id";
        String values = " values('" + IdGenerator.getIdStr() + "'";
        for (var col : entity.getCols()) {
            if (col.getIsPk() == 1) {
                continue;
            }
            String fieldName = col.getFieldName();
            //自动生成编号字段
            String serialNoId = col.getRefSerialNo();
            if (StringUtils.isNotEmpty(serialNoId)) {
                var service = SpringUtil.getBean("serialNoServiceImpl", SerialNoServiceImpl.class);
                String value = service.getSerialNoById(serialNoId);
                insert += "," + fieldName;
                values += ",'" + value + "'";
                continue;
            }
            if (Constants.COL_TENANT_ID.equals(fieldName)) {
                String tenantId = ContextUtil.getCurrentTenantId();
                if (StringUtils.isNotEmpty(tenantId)) {
                    insert += "," + fieldName;
                    values += ",'" + ContextUtil.getCurrentTenantId() + "'";
                }
                continue;
            }
            if (Constants.COL_CREATE_TIME.equals(fieldName)) {
                insert += "," + fieldName;
                values += ",'" + DateUtils.getTime() + "'";
                continue;
            }
            if (Constants.COL_CREATE_BY.equals(fieldName)) {
                insert += "," + fieldName;
                values += ",'" + ContextUtil.getCurrentUser().getAccount() + "'";
                continue;
            }
            String val = record.getString(fieldName);
            if (StringUtils.isEmpty(val)) {
                continue;
            }
            String colType = col.getColType();
            var type = DataTypeEnum.get(colType);
            //数值类型，值不用加单引号
            if (type.getType() == 1) {
                insert += "," + fieldName;
                values += "," + val;
            } else {
                insert += "," + fieldName;
                values += ",'" + val + "'";
            }
        }
        String sql = insert + ") " + values + ")";
        return executeNativeSql(sql, "新增记录出错");
    }

    /***
     * 修改实体表相关的记录
     * @param entity 实体对象，包括字段列定义
     * @param record 记录数据
     * @param pkId 主键值
     */
    @Override
    public boolean update(Entity entity, JSONObject record, String pkId) {
        String sql = "update " + entity.getTableName() + " set ";
        for (var col : entity.getCols()) {
            if (col.getIsPk() == 1) {
                continue;
            }
            String fieldName = col.getFieldName();
            if (Constants.COL_UPDATE_TIME.equals(fieldName)) {
                sql += fieldName + "='" + DateUtils.getTime() + "',";
                continue;
            }
            if (Constants.COL_UPDATE_BY.equals(fieldName)) {
                sql += fieldName + "='" + ContextUtil.getCurrentUser().getAccount() + "',";
                continue;
            }
            String val = record.getString(fieldName);
            if (StringUtils.isEmpty(val)) {
                continue;
            }
            String colType = col.getColType();
            var type = DataTypeEnum.get(colType);
            //数值类型，值不用加单引号
            if (type.getType() == 1) {
                sql += fieldName + "=" + val + ",";
            } else {
                sql += fieldName + "='" + val + "',";
            }
        }
        sql = sql.substring(0, sql.length() - 1) + " where id='" + pkId + "'";
        return executeNativeSql(sql, "修改记录出错");
    }

    /***
     * 将通用的SQL转换成当前数据库的SQL
     * @param sql 通用SQL
     * @return java.lang.String
     */
    @Override
    public String convert2Native(String sql) {
        return this.dialect.trans(sql);
    }

    /***
     * 获取分页查询SQL
     * @param sql 查询语句（未分页）
     * @param pageNo 页码
     * @param pageSize 每页记录数
     * @return java.lang.String
     */
    @Override
    public String getPagedSql(String sql, int pageNo, int pageSize) {
        return this.dialect.paginAndTrans(pageNo, pageSize, sql);
    }

    /***
     * @description 获取指定表名的表结构, 由 TableModelUtilsOfDb.db2Models方法改造
     * @param con 数据库连接
     * @param dialect 数据库方言
     * @param tableName 表名
     * @return com.github.drinkjava2.jdialects.model.TableModel
     * @author Elwin ZHANG
     * @date 2023/7/14 14:02
     */
    private TableModel db2Models(Connection con, Dialect dialect, String tableName) {
        SQLException sqlException = null;
        ResultSet rs = null;
        PreparedStatement pst = null;
        TableModel model = new TableModel(tableName);

        try {
            DatabaseMetaData meta = con.getMetaData();
            String catalog = con.getCatalog();
            // Build Columns
            rs = meta.getColumns(catalog, null, tableName, null); // detail see meta.getC alt + /
            while (rs.next()) {
                String colName = rs.getString("COLUMN_NAME");
                if (ReservedDBWords.isReservedWord(dialect, colName))
                    colName = dialect.ddlFeatures.getOpenQuote() + colName + dialect.ddlFeatures.getOpenQuote();
                ColumnModel col = new ColumnModel(colName);
                model.addColumn(col);

                int javaSqlType = rs.getInt("DATA_TYPE");
                try {
                    col.setColumnType(TypeUtils.javaSqlTypeToDialectType(javaSqlType));
                } catch (Exception e1) {
                    throw new DialectException("jDialect does not supported java.sql.types value " + javaSqlType, e1);
                }

                //col.setLength(rs.getInt("CHAR_OCTET_LENGTH"));
                col.setLength(rs.getInt("COLUMN_SIZE"));
                col.setPrecision(rs.getInt("COLUMN_SIZE"));
                col.setScale(rs.getInt("DECIMAL_DIGITS"));
                col.setNullable(rs.getInt("NULLABLE") > 0);
                col.setDefaultValue(rs.getString("COLUMN_DEF"));
                col.setComment(rs.getString("REMARKS"));
            }
            rs.close();

            //获取主键
            rs = meta.getPrimaryKeys(catalog, null, tableName);
            while (rs.next()) {
                model.getColumnByColName(rs.getString("COLUMN_NAME")).setPkey(true);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
            sqlException = e;
        } finally {
            if (pst != null)
                try {
                    pst.close();
                } catch (SQLException e1) {
                    if (sqlException != null)
                        sqlException.setNextException(e1);
                    else
                        sqlException = e1;
                }
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException e2) {
                if (sqlException != null)
                    sqlException.setNextException(e2);
                else
                    sqlException = e2;
            }
        }
        if (sqlException != null)
            throw new DialectException(sqlException);
        return model;
    }
}
